package myjavab;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

public class DBUtil {
	Connection con=null;
	Statement stmt=null;
	ResultSet rs=null;
	PreparedStatement ps=null;
	ArrayList<Student> arlist=new ArrayList<Student>();
	public Connection getConnection() {
		try {
			con= DriverManager.getConnection("jdbc:mysql://localhost/myclassdb?characterEncoding=utf8&serverTimezone=GMT%2B8", "root", "12");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "Connect error");
		}
		return con;
		
	}
	
	public ArrayList<Student> executeQueryAll() {
		try {
			ps=con.prepareStatement("select * from student order by score desc");
			rs=ps.executeQuery();
			arlist.clear();
			while(rs.next()) {
				Student stu=new Student(rs.getString(1), rs.getString(2), rs.getString(5), rs.getInt(3),rs.getInt(4));
				arlist.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "SQL error");
		}
		return arlist;
	}
	
	public ArrayList<Student> executeQueryByld(String id){
		try {
			ps=con.prepareStatement("select * from student where sid like '%"+id+"%' order by score desc");
			rs=ps.executeQuery();
			arlist.clear();
			while(rs.next()) {
				Student stu=new Student(rs.getString(1), rs.getString(2), rs.getString(5), rs.getInt(3),rs.getInt(4));
				arlist.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "SQL error");
		}
		return arlist;
	}
	
	public void insert(String sid,String sname,int age,int score,String sdept) {
		try {
			PreparedStatement psql=con.prepareStatement("insert into student values(?,?,?,?,?)");
			psql.setString(1, sid);
			psql.setString(2, sname);
			psql.setInt(3, age);
			psql.setInt(4, score);
			psql.setString(5, sdept);
			psql.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "insert error");
		}
		
	}
	
	public void update(String sid,String sname) {
		try {
			PreparedStatement psql = con.prepareStatement("update student set sname=? where sid=?");
			psql.setString(1, sname);
			psql.setString(2, sid);
			psql.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "update error");
		}
	}
	
	public void delete(String sid) {
		try {
			PreparedStatement psql = con.prepareStatement("delete from student where sid=?");
			psql.setString(1, sid);
			psql.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			JOptionPane.showMessageDialog(null, "delete error");
		}
		
	}
	

}
